USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetSQLPerformanceCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Sp_GetSQLPerformanceCount]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sp_GetSQLPerformanceCount]             
AS
BEGIN
  SET NOCOUNT ON
  DELETE FROM [ServerMonitor].[dbo].[SQLPerformanceCount]
  WHERE DATEDIFF(MONTH,LogTime,GETDATE()) > 2
  
  INSERT INTO [ServerMonitor].[dbo].[SQLPerformanceCount]
  SELECT 
  GETDATE() AS LogTime,
  MAX(CASE WHEN(CHARINDEX('User Connections',counter_name) > 0 AND CHARINDEX('General Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ConnectCount,
  MAX(CASE WHEN(CHARINDEX('Logins/sec',counter_name) > 0 AND CHARINDEX('General Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LoginPerSec,
  MAX(CASE WHEN(CHARINDEX('Logouts/sec',counter_name) > 0 AND CHARINDEX('General Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogoutPerSec,
  MAX(CASE WHEN(CHARINDEX('Batch Requests/sec',counter_name) > 0 AND CHARINDEX('SQL Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS RequestsPerSec,
  MAX(CASE WHEN(CHARINDEX('SQL Compilations/sec',counter_name) > 0 AND CHARINDEX('SQL Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS CompilationsPerSec,
  MAX(CASE WHEN(CHARINDEX('SQL Re-Compilations/sec',counter_name) > 0 AND CHARINDEX('SQL Statistics',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ReCompilationsPerSec,
  (MAX(CASE WHEN(CHARINDEX('Buffer cache hit ratio',counter_name) > 0 AND CHARINDEX('Buffer Manager',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) /
  MAX(CASE WHEN(CHARINDEX('Buffer cache hit ratio base',counter_name) > 0 AND CHARINDEX('Buffer Manager',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END)) AS BufferHitRatio,
  MAX(CASE WHEN(CHARINDEX('Page life expectancy',counter_name) > 0 AND CHARINDEX('Buffer Manager',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS PageLife,
  MAX(CASE WHEN(CHARINDEX('Target Server Memory (KB)',counter_name) > 0 AND CHARINDEX('Memory Manager',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS TagetMemoryKB,
  MAX(CASE WHEN(CHARINDEX('Total Server Memory (KB)',counter_name) > 0 AND CHARINDEX('Memory Manager',object_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS TotalMemoryKB,
  MAX(CASE WHEN(CHARINDEX('Lock Requests/sec',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LockRequestsPerSec,
  MAX(CASE WHEN(CHARINDEX('Lock Timeouts/sec',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LockTimeoutsPerSec,
  MAX(CASE WHEN(CHARINDEX('Number of Deadlocks/sec',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS Deadlocks,
  MAX(CASE WHEN(CHARINDEX('Lock Waits/sec',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS locksWaitsPerSec,
  MAX(CASE WHEN(CHARINDEX('Lock Wait Time (ms)',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS locksWaitsTime,
  MAX(CASE WHEN(CHARINDEX('Average Wait Time (ms)',counter_name) > 0 AND CHARINDEX('Locks',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LockAverageWaitsTime,
  MAX(CASE WHEN(CHARINDEX('Data File(s) Size (KB)',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS DataFileSizeKB,
  MAX(CASE WHEN(CHARINDEX('Log File(s) Size (KB)',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogFileSizeKB,
  MAX(CASE WHEN(CHARINDEX('Log File(s) Used Size (KB)',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogFileUsedSizeKB,
  MAX(CASE WHEN(CHARINDEX('Percent Log Used',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogPercent,
  MAX(CASE WHEN(CHARINDEX('Active Transactions',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ActiveTran,
  MAX(CASE WHEN(CHARINDEX('Transactions/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS TranPerSec,
  MAX(CASE WHEN(CHARINDEX('Repl. Pending Xacts',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ReplPendingXacts,
  MAX(CASE WHEN(CHARINDEX('Repl. Trans. Rate',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ReplTransRate,
  MAX(CASE WHEN(CHARINDEX('Log Cache Reads/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogCacheReadPerSec,
  MAX(CASE WHEN(CHARINDEX('Log Cache Hit Ratio',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogCacheHitRatio,
  MAX(CASE WHEN(CHARINDEX('Bulk Copy Rows/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS BulkCopyRowsPerSec,
  MAX(CASE WHEN(CHARINDEX('Bulk Copy Throughput/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS BulkCopuThroughputPerSec,
  MAX(CASE WHEN(CHARINDEX('Backup/Restore Throughput/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS BackupRestoreThroughtPerSec,
  MAX(CASE WHEN(CHARINDEX('DBCC Logical Scan Bytes/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS DBCCLogicalScanBytesPerSec,
  MAX(CASE WHEN(CHARINDEX('Shrink Data Movement Bytes/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS ShrinkDataMoveBytePerSec,
  MAX(CASE WHEN(CHARINDEX('Log Flushes/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogFlushesPerSec,
  MAX(CASE WHEN(CHARINDEX('Log Bytes Flushed/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogBytesFlushedPerSec,
  MAX(CASE WHEN(CHARINDEX('Log Flush Waits/sec',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogFlushWaitsPerSec,
  MAX(CASE WHEN(CHARINDEX('Log Flush Wait Time',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogFlushWaitTime,
  MAX(CASE WHEN(CHARINDEX('Log Truncations',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogTruncations,
  MAX(CASE WHEN(CHARINDEX('Log Growths',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogGrowths,
  MAX(CASE WHEN(CHARINDEX('Log Shrinks',counter_name) > 0 AND CHARINDEX('Databases',object_name) > 0 AND CHARINDEX('_Total',instance_name) > 0 ) 
       THEN cntr_value ELSE NULL 
  END) AS LogShrinks
  FROM master.dbo.sysperfinfo  
  SET NOCOUNT OFF
  RETURN 0
END


GO


